###############################################################################
# Name: KTaskTimerDB.py                                                       #
# Author: Marijo Mihelcic <marijo.mihelcic@gmail.com>                         #
# Copyright: (c) 2009 Marijo Mihelcic <marijo.mihelcic@gmail.com>             #
# Licence: GNU General Public License v3                                      #
###############################################################################

import sqlite3 as lite
import os

""" Database filename """
homedir     = os.path.expanduser('~')
db_dir      = '.simpletasktimer'
db_filename = 'tasktimer.db'
DB_FILENAME = os.path.join(homedir, db_dir, db_filename)

class CreateDB:
    def __init__(self):
        os.mkdir(os.path.join(homedir, db_dir))
        con = lite.connect(DB_FILENAME)
        cursor = con.cursor()
        cursor.execute("CREATE TABLE projects (project_id INTEGER PRIMARY KEY, project_name TEXT, created DATE)")
        cursor.execute("CREATE TABLE tasks (task_id INTEGER PRIMARY KEY, project_id INTEGER, task_name TEXT, task_description TEXT, created DATE)")
        cursor.execute("CREATE INDEX I_task_project ON tasks (project_id)")
        cursor.execute("CREATE TABLE tasks_progress (task_progress_id INTEGER PRIMARY KEY, task_id INTEGER, project_id INTEGER, progress_desc TEXT, start_date DATE, end_date DATE, time_worked INTEGER, created DATE)")
        cursor.execute("CREATE INDEX I_progress_task ON tasks_progress (task_id)")
        cursor.execute("CREATE INDEX I_progress_project ON tasks_progress (project_id)")
        cursor.execute("insert into projects values (0, 'No project', DATETIME('NOW'))")
        con.commit()
        con.close()

class DataSource:
    def __init__(self):
        if os.path.exists(DB_FILENAME) == False:
            CreateDB()
        self.con    = lite.connect(DB_FILENAME)
        #self.con.row_factory = lite.Row
        self.cursor = self.con.cursor()

        self.selected_project_id    = None
        self.selected_task_id       = None

    def closeConnection(self):
        self.con.close()

    def getProjectsLike(self, search_string=''):
        projects_list = {}
        if len(search_string) == 0:
            projects_list = self.getProjectsLists()
        else:
            self.cursor.execute("SELECT project_id, project_name FROM projects WHERE project_name LIKE '%' || ? || '%'  ORDER BY project_name DESC", (search_string, ))
            for row in self.cursor:
                projects_list[row[0]] = row[1]
        return projects_list

    def getProjectsLists(self):
        self.cursor.execute("SELECT project_id, project_name FROM projects ORDER BY project_name DESC")
        projects_list = {}
        for row in self.cursor:
            projects_list[row[0]] = row[1]
        return projects_list

    def getProjectName(self, project_id=0):
        project_name = ''
        if project_id == 0:
            project_id = self.selected_project_id
        self.cursor.execute("SELECT project_name FROM projects WHERE project_id=?", (project_id,))
        for row in self.cursor:
            project_name = row[0]
        return project_name

    def getProjectTasksLists(self, project_id, search_string=''):
        tasks_list = {}
        self.selected_project_id    = project_id
        self.selected_task_id       = None
        if len(search_string) == 0:
            self.cursor.execute("SELECT task_id, task_name FROM tasks WHERE project_id=? ORDER BY task_name DESC", (project_id,))
        else:
            self.cursor.execute("SELECT task_id, task_name FROM tasks WHERE project_id=? AND task_name LIKE '%' || ? || '%' ORDER BY task_name DESC", (project_id, search_string, ))
        for row in self.cursor:
            tasks_list[row[0]] = row[1]
        #print 'project_data: project_id:', self.selected_project_id, ' task_id:', self.selected_task_id
        return tasks_list

    def getTaskData(self, task_id=0):
        task_data = {}
        if task_id == 0:
            task_id = self.selected_task_id
        self.cursor.execute("SELECT task_name, task_description FROM tasks WHERE task_id=?", (task_id,))
        for row in self.cursor:
            task_data['name']           = row[0]
            task_data['description']    = row[1]

        #print 'taskdata: project_id:', self.selected_project_id, ' task_id:', self.selected_task_id
        return task_data


    def getTaskDescription(self, task_id):
        self.selected_task_id   = task_id
        task_description        = ""
        self.cursor.execute("SELECT task_description FROM tasks WHERE task_id=?", (task_id,))
        for row in self.cursor:
            task_description = row[0]

        #print 'taskdesc: project_id:', self.selected_project_id, ' task_id:', self.selected_task_id
        return task_description

    def getSelectedProjectTaskData(self, project_id=0, task_id=0):
        project_name    = ''
        task_name       = ''

        if project_id == 0:
            project_id = self.selected_project_id

        if task_id == 0:
            task_id = self.selected_task_id

        self.cursor.execute("SELECT project_name FROM projects WHERE project_id=?", (project_id,))
        for row in self.cursor:
            project_name = row[0]
        self.cursor.execute("SELECT task_name FROM tasks WHERE task_id=?", (task_id,))
        for row in self.cursor:
            task_name = row[0]
        return {"project_id" : project_id, "project_name" : project_name, "task_id" : task_id, "task_name" : task_name}

    def getSelectedProjectTask(self):
        return {"project_id" : self.selected_project_id, "task_id" : self.selected_task_id}

    def _updateProject(self, project_id, project_name):
        self.cursor.execute("UPDATE projects SET project_name=? WHERE project_id=?", (project_name, project_id, ))
        self.con.commit()

    def _insertProject(self, project_name):
        self.cursor.execute("INSERT INTO projects (project_name, created) VALUES(?, DATETIME('NOW'))", (project_name, ))
        self.con.commit()

    def storeProjectData(self, project_data={}):
        if project_data['action'] == 'edit':
            self._updateProject(project_data['project_id'], project_data['project_name'])
        elif project_data['action'] == 'new':
            self._insertProject(project_data['project_name'])

    def _updateTask(self, project_id, task_id, task_name, task_description):
        self.cursor.execute("UPDATE tasks SET project_id=?, task_name=?, task_description=? WHERE task_id=?", (project_id, task_name, task_description, task_id, ))
        self.con.commit()
        self.cursor.execute("UPDATE tasks_progress SET project_id=? WHERE task_id=?", (project_id, task_id, ))
        self.con.commit()

    def _insertTask(self, project_id, task_name, task_description):
        self.cursor.execute("INSERT INTO tasks (project_id, task_name, task_description, created) VALUES(?, ?, ?, DATETIME('NOW'))", (project_id, task_name, task_description, ))
        self.con.commit()

    def storeTaskData(self, task_data={}):
        if task_data['action'] == 'edit':
            self._updateTask(task_data['project_id'], task_data['task_id'], task_data['task_name'], task_data['task_description'])
        elif task_data['action'] == 'new':
            self._insertTask(task_data['project_id'], task_data['task_name'], task_data['task_description'])

    def _deleteTaskProgress(self, delete_type):
        #print "delete task progress"
        if delete_type['type'] == 'project':
            self.cursor.execute("DELETE FROM tasks_progress WHERE project_id=?", (self.selected_project_id,))
        else:
            self.cursor.execute("DELETE FROM tasks_progress WHERE task_id=?", (self.selected_task_id,))
        self.con.commit()

    def _deleteTasks(self, delete_type):
        #print "delete task"
        if delete_type['type'] == 'project':
            self.cursor.execute("DELETE FROM tasks WHERE project_id=?", (self.selected_project_id,))
        else:
            self.cursor.execute("DELETE FROM tasks WHERE task_id=?", (self.selected_task_id,))
        self.con.commit()

    def _deleteProject(self):
        #print "delete project"
        self.cursor.execute("DELETE FROM projects WHERE project_id=?", (self.selected_project_id,))
        self.con.commit()

    def deleteData(self, delete_type):
        #print "delete data ", delete_type
        self._deleteTaskProgress(delete_type)
        self._deleteTasks(delete_type)
        if delete_type['type'] == 'project':
            self._deleteProject()

    def storeProgressData(self, store_data):
        self.cursor.execute("insert into tasks_progress (task_id, project_id, progress_desc, start_date, end_date, time_worked, created) values (?, ?, ?, ?, ?, ?, DATETIME('NOW'))",
                            (store_data['task_id'], store_data['project_id'], store_data['progress'], store_data['start_time'], store_data['stop_time'], store_data['total_time'],))
        self.con.commit()

    def getReports(self, report_data):
        task_progress_data = {}
        if report_data['project_id'] == None:
            self.cursor.execute("""SELECT
                                    p.project_id as project_id,
                                    p.project_name as project_name,
                                    t.task_id as task_id,
                                    t.task_name as task_name,
                                    tp.task_progress_id as task_progress_id,
                                    tp.progress_desc as progress_desc,
                                    tp.start_date as start_date,
                                    tp.end_date as end_date,
                                    tp.time_worked as time_worked
                                FROM
                                    projects p,
                                    tasks t,
                                    tasks_progress tp
                                WHERE
                                    tp.start_date >=? AND
                                    tp.end_date <=? AND
                                    tp.project_id = p.project_id AND
                                    tp.task_id = t.task_id
                                ORDER BY
                                    p.project_name ASC,
                                    t.task_name ASC,
                                    tp.created ASC""", (report_data['date_from'], report_data['date_to'],))
        else:
            self.cursor.execute("""SELECT
                                    p.project_id as project_id,
                                    p.project_name as project_name,
                                    t.task_id as task_id,
                                    t.task_name as task_name,
                                    tp.task_progress_id as task_progress_id,
                                    tp.progress_desc as progress_desc,
                                    tp.start_date as start_date,
                                    tp.end_date as end_date,
                                    tp.time_worked as time_worked
                                FROM
                                    projects p,
                                    tasks t,
                                    tasks_progress tp
                                WHERE
                                    tp.project_id=? AND
                                    tp.start_date >=? AND
                                    tp.end_date <=? AND
                                    tp.project_id = p.project_id AND
                                    tp.task_id = t.task_id
                                ORDER BY
                                    p.project_name ASC,
                                    t.task_name ASC,
                                    tp.created ASC""", (report_data['project_id'], report_data['date_from'], report_data['date_to'],))

        for row in self.cursor:
            progress_data = {
                    "description"   : row[5],
                    "start_date"    : row[6],
                    "end_date"      : row[7],
                    "time_worked"   : row[8]
            }

            if task_progress_data.has_key(row[0]) == False:
                task_progress_data[row[0]] = {}
            task_progress_data[row[0]]['title'] = row[1]
            if task_progress_data[row[0]].has_key('tasks') == False:
                task_progress_data[row[0]]['tasks'] = {}
            if task_progress_data[row[0]]['tasks'].has_key(row[2]) == False:
                task_progress_data[row[0]]['tasks'][row[2]] = {}
            task_progress_data[row[0]]['tasks'][row[2]]['title'] = row[3]
            if task_progress_data[row[0]]['tasks'][row[2]].has_key('task_progress') == False:
                task_progress_data[row[0]]['tasks'][row[2]]['task_progress'] = {}
            if task_progress_data[row[0]]['tasks'][row[2]]['task_progress'].has_key(row[4]) == False:
                task_progress_data[row[0]]['tasks'][row[2]]['task_progress'][row[4]] = {}
            task_progress_data[row[0]]['tasks'][row[2]]['task_progress'][row[4]].update(progress_data)

        return task_progress_data
